--
-- PostgreSQL database dump
--

-- Started on 2011-06-28 23:39:42 BRT

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

--
-- TOC entry 342 (class 1247 OID 17614)
-- Dependencies: 3
-- Name: dm_cpf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_cpf AS character(14) NOT NULL;


ALTER DOMAIN public.dm_cpf OWNER TO postgres;

--
-- TOC entry 340 (class 1247 OID 17612)
-- Dependencies: 3
-- Name: dm_local; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_local AS character varying(80);


ALTER DOMAIN public.dm_local OWNER TO postgres;

--
-- TOC entry 337 (class 1247 OID 17609)
-- Dependencies: 3
-- Name: dm_nome; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome AS character varying(100) NOT NULL;


ALTER DOMAIN public.dm_nome OWNER TO postgres;

--
-- TOC entry 338 (class 1247 OID 17610)
-- Dependencies: 3
-- Name: dm_nome2; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome2 AS character varying(100);


ALTER DOMAIN public.dm_nome2 OWNER TO postgres;

--
-- TOC entry 343 (class 1247 OID 17615)
-- Dependencies: 3
-- Name: dm_rg; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_rg AS character(12) NOT NULL;


ALTER DOMAIN public.dm_rg OWNER TO postgres;

--
-- TOC entry 339 (class 1247 OID 17611)
-- Dependencies: 3
-- Name: dm_telefone; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_telefone AS character varying(14);


ALTER DOMAIN public.dm_telefone OWNER TO postgres;

--
-- TOC entry 341 (class 1247 OID 17613)
-- Dependencies: 3
-- Name: dm_uf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_uf AS character(2) NOT NULL;


ALTER DOMAIN public.dm_uf OWNER TO postgres;

--
-- TOC entry 405 (class 1247 OID 19250)
-- Dependencies: 3 1625
-- Name: tp_cliente_profissional; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_cliente_profissional AS (
	codsolicitacao bigint,
	codcliente bigint,
	nomecliente dm_nome,
	codprofissional integer,
	nomeprofissional dm_nome,
	nivel smallint,
	aceito boolean
);


ALTER TYPE public.tp_cliente_profissional OWNER TO postgres;

--
-- TOC entry 385 (class 1247 OID 18619)
-- Dependencies: 3 1615
-- Name: tp_profissional_profissao; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_profissional_profissao AS (
	codprofissional integer,
	nome dm_nome,
	codprofissao integer,
	profissao dm_nome,
	nivel smallint,
	alocado boolean
);


ALTER TYPE public.tp_profissional_profissao OWNER TO postgres;

--
-- TOC entry 391 (class 1247 OID 18659)
-- Dependencies: 3 1618
-- Name: tp_solicitacoes; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_solicitacoes AS (
	codsolicitacao bigint,
	codcliente bigint,
	cliente dm_nome,
	quantidade integer,
	codprofissao integer,
	profissao dm_nome,
	datasolicitacao date,
	dataatendimento date
);


ALTER TYPE public.tp_solicitacoes OWNER TO postgres;

--
-- TOC entry 52 (class 1255 OID 19233)
-- Dependencies: 407 3
-- Name: atualizaalocacaoprofissional(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION atualizaalocacaoprofissional() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
	begin
	
		update profissional set alocado=new.aceito where codprofissional = new.profissionalcodprofissional;
		
		if (new.aceito) then
--			RAISE NOTICE 'update solicitacao set DataAtendimento = % where codsolicitacao = %;', current_date, new.SolicitacaoCodSolicitacao;
			update solicitacao set DataAtendimento = current_date where codsolicitacao = new.SolicitacaoCodSolicitacao;
		end if;

		return new;
	end;
$$;


ALTER FUNCTION public.atualizaalocacaoprofissional() OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 18606)
-- Dependencies: 3 407
-- Name: atualizanivelprofissional(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION atualizanivelprofissional() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
		profissao int;
	begin
		if new.nivel <> old.nivel then
			select into profissao profissaocodprofissao
			from solicitacao
			where codsolicitacao = new.solicitacaocodsolicitacao;
	
			update especializacao set nivel = (select avg(new.nivel) 
								from clientesprofissional cp, solicitacao s
								where s.profissaocodprofissao = profissao and
								cp.profissionalcodprofissional = new.profissionalcodprofissional and
								cp.aceito = 'true')
			where especializacao.profissionalcodprofissional = new.profissionalcodprofissional and
				especializacao.profissaocodprofissao = profissao;
		end if;
		return new;
	end;
$$;


ALTER FUNCTION public.atualizanivelprofissional() OWNER TO postgres;

--
-- TOC entry 46 (class 1255 OID 18946)
-- Dependencies: 3 407
-- Name: fn_atualiza_area(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualiza_area(integer, novonome text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- atualizando
	update area set descricao = novoNome where codarea = $1;
	
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	RETURN var_sistema > 0;
END;
$_$;


ALTER FUNCTION public.fn_atualiza_area(integer, novonome text) OWNER TO postgres;

--
-- TOC entry 24 (class 1255 OID 18051)
-- Dependencies: 3
-- Name: fn_atualizanivel(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualizanivel(integer, integer, integer) RETURNS smallint
    LANGUAGE sql
    AS $_$
	-- atualizando
	UPDATE Especializacao set nivel = nivel + $3 
		WHERE ProfissionalCodProfissional = $1 AND profissaoCodProfissao = $2;
	
	-- retornando nivel
	select nivel from vw_profissional_profissao
		where codprofissional = 1 and codprofissao = 1
		limit 1
$_$;


ALTER FUNCTION public.fn_atualizanivel(integer, integer, integer) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 19254)
-- Dependencies: 3 407
-- Name: fn_atualizanivelcontrato(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualizanivelcontrato(integer, integer, integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
	--select * from ClientesProfissional
	--select * from vw_profissional_profissao where codprofissional = 1
BEGIN
	update ClientesProfissional set nivel = $3 where SolicitacaoCodSolicitacao = $1 and ProfissionalCodProfissional = $2;
	RETURN FOUND;
END;
$_$;


ALTER FUNCTION public.fn_atualizanivelcontrato(integer, integer, integer) OWNER TO postgres;

--
-- TOC entry 31 (class 1255 OID 19260)
-- Dependencies: 407 3
-- Name: fn_atualizar_profissao(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualizar_profissao(integer, novonome text, novaarea integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- atualizando
	update profissao p set descricao = novoNome, areaCodArea = novaArea where codProfissao = $1;
	
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	RETURN var_sistema > 0;
END;
$_$;


ALTER FUNCTION public.fn_atualizar_profissao(integer, novonome text, novaarea integer) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1595 (class 1259 OID 17864)
-- Dependencies: 3 337
-- Name: area; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE area (
    codarea integer NOT NULL,
    descricao dm_nome
);


ALTER TABLE public.area OWNER TO postgres;

--
-- TOC entry 1597 (class 1259 OID 17875)
-- Dependencies: 337 3
-- Name: profissao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissao (
    codprofissao integer NOT NULL,
    descricao dm_nome NOT NULL,
    areacodarea integer NOT NULL
);


ALTER TABLE public.profissao OWNER TO postgres;

--
-- TOC entry 1606 (class 1259 OID 17964)
-- Dependencies: 1712 3 337 337
-- Name: vw_area_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_area_profissao AS
    SELECT a.codarea, a.descricao AS area, p.codprofissao, p.descricao AS profissao FROM (area a JOIN profissao p ON ((a.codarea = p.areacodarea)));


ALTER TABLE public.vw_area_profissao OWNER TO postgres;

--
-- TOC entry 32 (class 1255 OID 19261)
-- Dependencies: 367 3
-- Name: fn_busca_profissao(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_busca_profissao(integer) RETURNS SETOF vw_area_profissao
    LANGUAGE sql
    AS $_$

	select * from vw_area_profissao
		where codProfissao = $1
		Limit 1;
$_$;


ALTER FUNCTION public.fn_busca_profissao(integer) OWNER TO postgres;

--
-- TOC entry 49 (class 1255 OID 19236)
-- Dependencies: 407 3
-- Name: fn_deletar_area(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_deletar_area(codarea integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.	
BEGIN
	delete from profissao where AreaCodArea = codArea;
	delete from area where area.codArea = codArea;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	return var_sistema > 0;
END;
$$;


ALTER FUNCTION public.fn_deletar_area(codarea integer) OWNER TO postgres;

--
-- TOC entry 39 (class 1255 OID 19264)
-- Dependencies: 407 3
-- Name: fn_deletar_profissao(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_deletar_profissao(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.	
BEGIN
	delete from profissao where CodProfissao = $1;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	return var_sistema > 0;
END;
$_$;


ALTER FUNCTION public.fn_deletar_profissao(integer) OWNER TO postgres;

--
-- TOC entry 51 (class 1255 OID 19244)
-- Dependencies: 3
-- Name: fn_existe_cliente_cpfcnpj(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_existe_cliente_cpfcnpj(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
	select count(cpfcnpj) > 0 from cliente where cpfcnpj = $1
$_$;


ALTER FUNCTION public.fn_existe_cliente_cpfcnpj(text) OWNER TO postgres;

--
-- TOC entry 47 (class 1255 OID 18947)
-- Dependencies: 3
-- Name: fn_existe_profissional_cpf(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_existe_profissional_cpf(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
	select count(cpf) > 0 from profissional where cpf = $1
$_$;


ALTER FUNCTION public.fn_existe_profissional_cpf(text) OWNER TO postgres;

--
-- TOC entry 50 (class 1255 OID 19239)
-- Dependencies: 3
-- Name: fn_existe_users_login(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_existe_users_login(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
	select count(login) > 0 from users where login = $1
$_$;


ALTER FUNCTION public.fn_existe_users_login(text) OWNER TO postgres;

--
-- TOC entry 37 (class 1255 OID 19255)
-- Dependencies: 405 3
-- Name: fn_filtra_cliente_profissional(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtra_cliente_profissional(integer, integer) RETURNS SETOF tp_cliente_profissional
    LANGUAGE sql
    AS $_$
	select * from vw_cliente_profissional where codSolicitacao = $1 and codCliente = $2;
$_$;


ALTER FUNCTION public.fn_filtra_cliente_profissional(integer, integer) OWNER TO postgres;

--
-- TOC entry 1599 (class 1259 OID 17886)
-- Dependencies: 1921 1922 1923 1924 1925 1926 1927 337 3 343 340 339 339 342 338 341 340 340
-- Name: profissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissional (
    codprofissional integer NOT NULL,
    cpf dm_cpf,
    nome dm_nome,
    fantasia dm_nome2,
    sexo character(1) NOT NULL,
    fonefixo dm_telefone,
    fonecel dm_telefone,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    rg_num dm_rg,
    rg_org_exp character(6) NOT NULL,
    rg_data_exp date NOT NULL,
    carteiraprofissional character varying(14) NOT NULL,
    cnh_registro character varying(12),
    cnh_cat character varying(10),
    cnh_vencto date,
    datanascimento date NOT NULL,
    alocado boolean NOT NULL,
    CONSTRAINT valida_cnh CHECK ((cnh_vencto < ('now'::text)::date)),
    CONSTRAINT valida_cpf CHECK (((length((cpf)::bpchar) = 14) AND ((uf)::bpchar <> ' '::bpchar))),
    CONSTRAINT valida_expedicao CHECK ((rg_data_exp < ('now'::text)::date)),
    CONSTRAINT valida_fantasia CHECK (((fantasia)::text <> (nome)::text)),
    CONSTRAINT valida_fone CHECK (((fonefixo)::text <> (fonecel)::text)),
    CONSTRAINT valida_rg CHECK (((rg_num)::bpchar <> ' '::bpchar)),
    CONSTRAINT valida_uf CHECK (((length((uf)::text) = 2) AND ((uf)::bpchar <> ' '::bpchar)))
);


ALTER TABLE public.profissional OWNER TO postgres;

--
-- TOC entry 29 (class 1255 OID 18694)
-- Dependencies: 353 3
-- Name: fn_filtra_profissionais(integer[], integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtra_profissionais(integer[], integer) RETURNS SETOF profissional
    LANGUAGE sql
    AS $_$
	select * from vw_profissionais_disponiveis v
		where 	v.codProfissional = ANY($1)
				and v.codProfissional IN (SELECT codProfissional FROM vw_profissional_profissao where CodProfissao = $2)
$_$;


ALTER FUNCTION public.fn_filtra_profissionais(integer[], integer) OWNER TO postgres;

--
-- TOC entry 28 (class 1255 OID 18581)
-- Dependencies: 345 3
-- Name: fn_filtrar_area(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area(text) RETURNS SETOF area
    LANGUAGE sql
    AS $_$
	SELECT * FROM Area WHERE LOWER(descricao) LIKE fn_NoCase($1)
$_$;


ALTER FUNCTION public.fn_filtrar_area(text) OWNER TO postgres;

--
-- TOC entry 1600 (class 1259 OID 17897)
-- Dependencies: 1928 3
-- Name: especializacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE especializacao (
    profissionalcodprofissional integer NOT NULL,
    profissaocodprofissao integer NOT NULL,
    dataespecializacao date NOT NULL,
    nivel smallint NOT NULL,
    CONSTRAINT valida_nivel2 CHECK (((nivel <= 5) AND (nivel >= 1)))
);


ALTER TABLE public.especializacao OWNER TO postgres;

--
-- TOC entry 1607 (class 1259 OID 17968)
-- Dependencies: 1713 3 337 337
-- Name: vw_profissional_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_profissional_profissao AS
    SELECT profissional.codprofissional, profissional.nome, profissao.codprofissao, profissao.descricao AS profissao, especializacao.nivel, profissional.alocado FROM ((especializacao JOIN profissional ON ((profissional.codprofissional = especializacao.profissionalcodprofissional))) JOIN profissao ON ((profissao.codprofissao = especializacao.profissaocodprofissao)));


ALTER TABLE public.vw_profissional_profissao OWNER TO postgres;

--
-- TOC entry 1612 (class 1259 OID 18003)
-- Dependencies: 1718 337 3 337
-- Name: vwr_profissionais_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_profissionais_solicitacao AS
    SELECT vw_profissional_profissao.profissao, vw_profissional_profissao.nome, vw_profissional_profissao.nivel FROM vw_profissional_profissao ORDER BY vw_profissional_profissao.codprofissao, vw_profissional_profissao.nivel DESC, vw_profissional_profissao.nome;


ALTER TABLE public.vwr_profissionais_solicitacao OWNER TO postgres;

--
-- TOC entry 27 (class 1255 OID 18538)
-- Dependencies: 379 3
-- Name: fn_filtrar_area_quantidade(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area_quantidade(text, text) RETURNS SETOF vwr_profissionais_solicitacao
    LANGUAGE sql
    AS $_$
	select * from vwr_profissionais_solicitacao
		where lower(profissao) like fn_NoCase($1)
		and lower(nome) like fn_NoCase($2)

$_$;


ALTER FUNCTION public.fn_filtrar_area_quantidade(text, text) OWNER TO postgres;

--
-- TOC entry 53 (class 1255 OID 18582)
-- Dependencies: 3 349
-- Name: fn_filtrar_profissao(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_profissao(integer) RETURNS SETOF profissao
    LANGUAGE sql
    AS $_$
	select * from Profissao where AreaCodArea = $1 order by codProfissao
$_$;


ALTER FUNCTION public.fn_filtrar_profissao(integer) OWNER TO postgres;

--
-- TOC entry 38 (class 1255 OID 19263)
-- Dependencies: 349 3
-- Name: fn_filtrar_profissao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_profissao(text) RETURNS SETOF profissao
    LANGUAGE sql
    AS $_$
	select * from Profissao where lower(descricao) like fn_NoCase($1) order by codProfissao; 
	--select * from  vw_area_profissao where lower(profissao) like fn_NoCase($1);
$_$;


ALTER FUNCTION public.fn_filtrar_profissao(text) OWNER TO postgres;

--
-- TOC entry 26 (class 1255 OID 18056)
-- Dependencies: 3
-- Name: fn_get_area_profissao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_get_area_profissao(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select AreaCodArea from Profissao where Profissao.Descricao = $1 limit 1;
$_$;


ALTER FUNCTION public.fn_get_area_profissao(text) OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 18047)
-- Dependencies: 3
-- Name: fn_getclienteid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getclienteid(text) RETURNS bigint
    LANGUAGE sql
    AS $_$
	select CodCliente from Cliente where nomecli = $1;
$_$;


ALTER FUNCTION public.fn_getclienteid(text) OWNER TO postgres;

--
-- TOC entry 23 (class 1255 OID 18048)
-- Dependencies: 3
-- Name: fn_getprofissionalid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getprofissionalid(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select CodProfissional from Profissional where nome = $1;
$_$;


ALTER FUNCTION public.fn_getprofissionalid(text) OWNER TO postgres;

--
-- TOC entry 40 (class 1255 OID 19245)
-- Dependencies: 407 3 339 340 340 339 341 340 340 339 340 338 337
-- Name: fn_inserir_clientes(dm_nome, dm_nome2, dm_local, dm_local, dm_local, dm_uf, dm_telefone, dm_telefone, dm_telefone, dm_local, dm_local, character, date, character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_clientes(xnomecli dm_nome, xfantasia dm_nome2, xendereco dm_local, xbairro dm_local, xcidade dm_local, xuf dm_uf, xfonefixo dm_telefone, xfonefax dm_telefone, xfonecelular dm_telefone, xemail dm_local, xhomepage dm_local, xtipo character, xdatanasctoabertura date, xcpfcnpj character varying, xrgie character varying, loginusers character varying, senhauser character varying) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	codcli integer;
BEGIN
	INSERT INTO cliente (
nomecli,
fantasia,
endereco,
bairro,
cidade,
uf,
fonefixo,
fonefax,
fonecelular,
email,
homepage,
tipo,
datanasctoabertura,
cpfcnpj,
rgie
)
	VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15);

	if not FOUND then
		return found;
	end if;

	select into codcli codcliente from cliente where cpfcnpj = $14 and rgie = $15;

	--INSERT INTO users (nome,tipo,codigo,ativo,login) values ($2,'C',codcli,true,$16);
	INSERT INTO users (nome,tipo,codigo,ativo,login, senha) values (xnomecli,'C',codcli,true,loginUsers, senhaUser);
	return found;
END;
$_$;


ALTER FUNCTION public.fn_inserir_clientes(xnomecli dm_nome, xfantasia dm_nome2, xendereco dm_local, xbairro dm_local, xcidade dm_local, xuf dm_uf, xfonefixo dm_telefone, xfonefax dm_telefone, xfonecelular dm_telefone, xemail dm_local, xhomepage dm_local, xtipo character, xdatanasctoabertura date, xcpfcnpj character varying, xrgie character varying, loginusers character varying, senhauser character varying) OWNER TO postgres;

--
-- TOC entry 44 (class 1255 OID 18057)
-- Dependencies: 407 3
-- Name: fn_inserir_especializacao(text, text, date, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_profissional integer;
	cod_profissao integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verifica se tem a area da profissao
	select into cod_profissao CodProfissao from Profissao where Profissao.Descricao = nova_profissao;
	
	if FOUND then
		-- verifica se tem o profissional
		cod_profissional := fn_getProfissionalId(novo_profissional);
		--raise notice 'quanto deu o found %, %', FOUND, cod_profissional;
		--raise notice 'FOUND and % = %', (cod_profissional), (FOUND and cod_profissional>0);
		if (FOUND and cod_profissional>0) then
			--raise notice 'INSERT INTO Especializacao (ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) VALUES (%,%,%,%);', cod_profissional, cod_profissao, data_especializacao, novo_nivel;
			INSERT INTO Especializacao
				(ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) 
				VALUES (cod_profissional, cod_profissao, data_especializacao, novo_nivel);	
			GET DIAGNOSTICS var_sistema := ROW_COUNT;
			return var_sistema > 0;
		end if;
	end if;
	return false;
END;
$$;


ALTER FUNCTION public.fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) OWNER TO postgres;

--
-- TOC entry 25 (class 1255 OID 18054)
-- Dependencies: 407 3
-- Name: fn_inserir_profissao(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_profissao(nova_area text, nova_profissao text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_area integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verificar se existe area
	select into cod_area * from area where area.Descricao = nova_area;

	if FOUND then
		-- inserir profissao na area
		INSERT INTO Profissao(Descricao, AreaCodArea) VALUES (nova_profissao, cod_area);
	else
		-- criar area e inserir (recursivo)
		INSERT INTO Area(Descricao) VALUES (nova_area);
		return fn_inserir_profissao(nova_area, nova_profissao);
	end if;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	
	return var_sistema > 0;
END;
$$;


ALTER FUNCTION public.fn_inserir_profissao(nova_area text, nova_profissao text) OWNER TO postgres;

--
-- TOC entry 36 (class 1255 OID 19237)
-- Dependencies: 337 343 341 340 340 340 339 339 338 342 407 3
-- Name: fn_inserir_profissional(dm_cpf, dm_nome, dm_nome2, character, dm_telefone, dm_telefone, dm_local, dm_local, dm_local, dm_uf, dm_rg, character, date, character varying, character varying, character varying, date, date, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_profissional(xcpf dm_cpf, xnome dm_nome, xfantasia dm_nome2, xsexo character, xfonefixo dm_telefone, xfonecel dm_telefone, xendereco dm_local, xbairro dm_local, xcidade dm_local, xuf dm_uf, xrg_num dm_rg, xrg_org_exp character, xrg_data_exp date, xcarteiraprofissional character varying, xcnh_registro character varying, xcnh_cat character varying, xcnh_vencto date, xdatanascimento date, loginusers character varying, senhauser character varying) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	codprof integer;
BEGIN
	INSERT INTO profissional (
cpf,
nome,
fantasia,
sexo,
fonefixo,
fonecel,
endereco,
bairro,
cidade,
uf,
rg_num,
rg_org_exp,
rg_data_exp,
carteiraprofissional,
cnh_registro,
cnh_cat,
cnh_vencto,
datanascimento,
alocado)
	VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,false);

	if not FOUND then
		return found;
	end if;

	select into codprof codprofissional from profissional where cpf = $1;

	INSERT INTO users (nome,tipo,codigo,ativo,login, senha) values (xnome,'P',codprof,true,loginUsers, senhaUser);

	return found;
END;
$_$;


ALTER FUNCTION public.fn_inserir_profissional(xcpf dm_cpf, xnome dm_nome, xfantasia dm_nome2, xsexo character, xfonefixo dm_telefone, xfonecel dm_telefone, xendereco dm_local, xbairro dm_local, xcidade dm_local, xuf dm_uf, xrg_num dm_rg, xrg_org_exp character, xrg_data_exp date, xcarteiraprofissional character varying, xcnh_registro character varying, xcnh_cat character varying, xcnh_vencto date, xdatanascimento date, loginusers character varying, senhauser character varying) OWNER TO postgres;

--
-- TOC entry 45 (class 1255 OID 18697)
-- Dependencies: 3 407
-- Name: fn_inserir_solicitacao(integer, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_solicitacao(codsolicitacao integer, indicacoes integer[]) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
	row_solicitacao record; -- aqui eh para saber se pode contratar.  (qntde = indicacoes.length, DataAtendimento < hj ou nao nulo
	tempProf record; -- iterador
	
	qtdeProf integer; -- quantidade de profissinais disponiveis para alocar (baseado na array indicacoes)
	var_sistema integer; -- variavel q recebe valores do sistema.
	
BEGIN
	
	select into row_solicitacao * from Solicitacao s where s.CodSolicitacao = codSolicitacao;
	if FOUND then
		RAISE NOTICE 'row_solicitacao = %, %, %, %, %', row_solicitacao.ClienteCodCliente, row_solicitacao.ProfissaoCodProfissao, row_solicitacao.Qtde, row_solicitacao.DataSolicitacao, row_solicitacao.DataAtendimento;
		
		
		-- ja foi atendida?
		if (row_solicitacao.DataAtendimento is null) then
			-- nao, entao tenta contratar os caras.

			-- vendo quantas pessoas podem ser contradadas..
			select into qtdeProf COUNT(*) from fn_filtra_profissionais(indicacoes, row_solicitacao.ProfissaoCodProfissao);

			-- o numero de pessoas disponiveis batem com o numero solicitado?
			if (qtdeProf = row_solicitacao.qtde) then
				-- sim, entao contrata!
				for tempProf in select codprofissional from fn_filtra_profissionais (indicacoes, row_solicitacao.ProfissaoCodProfissao) loop
					-- RAISE NOTICE 'INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (%,%,%,%)', row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1;
					INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1) ;
				end loop;
				
				RAISE NOTICE 'Contratacao sucedida';
				return 1;
			else 
				-- nao, entao avisa quandos precisa de verdade
				RAISE NOTICE 'achou % mas precisa de %', qtdeProf, row_solicitacao.qtde;
				
				-- quantidade nao bate
				return -1;
			end if;
				
			
			RAISE NOTICE 'achou: %', qtdeProf;
		end if;
		-- sim, entao retorna false
		
		-- solicitacao ja atendida
		RETURN -2;
	end if;
	-- solicitacao nao encontrada.
	RETURN -3; 
	--INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) 
		--values();
	
END;
$$;


ALTER FUNCTION public.fn_inserir_solicitacao(codsolicitacao integer, indicacoes integer[]) OWNER TO postgres;

--
-- TOC entry 35 (class 1255 OID 18622)
-- Dependencies: 3 385
-- Name: fn_lista_especialista(integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especialista(integer, boolean) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao 
		where codprofissao = $1
			and alocado = $2
			order by nivel desc
$_$;


ALTER FUNCTION public.fn_lista_especialista(integer, boolean) OWNER TO postgres;

--
-- TOC entry 33 (class 1255 OID 18620)
-- Dependencies: 385 3
-- Name: fn_lista_especializacao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where nome = $1;
$_$;


ALTER FUNCTION public.fn_lista_especializacao(text) OWNER TO postgres;

--
-- TOC entry 34 (class 1255 OID 18621)
-- Dependencies: 3 385
-- Name: fn_lista_especializacao_avancado(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao_avancado(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where lower(nome) like lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_lista_especializacao_avancado(text) OWNER TO postgres;

--
-- TOC entry 41 (class 1255 OID 18683)
-- Dependencies: 391 3
-- Name: fn_lista_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(text) RETURNS SETOF tp_solicitacoes
    LANGUAGE sql
    AS $_$
	select * from vw_solicitacoes 
		where lower(cliente) like lower('%' || $1 || '%')
$_$;


ALTER FUNCTION public.fn_lista_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 42 (class 1255 OID 18684)
-- Dependencies: 391 3 407
-- Name: fn_lista_solicitacoes(integer, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(codcliente integer, atendida boolean) RETURNS SETOF tp_solicitacoes
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	
	if atendida then
		return query
			select * from vw_solicitacoes t
				where DataAtendimento > DataSolicitacao
					and t.CodCliente = codCliente
				;
	else
		return  query
			select * from vw_solicitacoes t
				where DataAtendimento < DataSolicitacao 
					or DataAtendimento is null
					and t.CodCliente = codCliente
					;
	end if;
	
END;
$$;


ALTER FUNCTION public.fn_lista_solicitacoes(codcliente integer, atendida boolean) OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 18537)
-- Dependencies: 3
-- Name: fn_nocase(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_nocase(text) RETURNS text
    LANGUAGE sql
    AS $_$
	select lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_nocase(text) OWNER TO postgres;

--
-- TOC entry 43 (class 1255 OID 18685)
-- Dependencies: 3
-- Name: fn_quantidade_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_quantidade_solicitacoes(text) RETURNS TABLE(codcliente bigint, cliente character varying, quantidade bigint)
    LANGUAGE sql
    AS $_$
	select codcliente, cliente, sum(quantidade) from vw_solicitacoes 
		--where lower(cliente) like lower('%' || $1 || '%')
		where cliente = $1
		group by codcliente,cliente
$_$;


ALTER FUNCTION public.fn_quantidade_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 48 (class 1255 OID 19231)
-- Dependencies: 3 407
-- Name: fn_verifica_nome(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_verifica_nome() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE 
BEGIN
	--RAISE NOTICE 'ta inserindo: %', new.descricao;
	PERFORM * from profissao where lower(descricao) = lower(new.descricao) and new.codProfissao <> codProfissao;
	if (FOUND) then
		rollback;
		return null;
	else 
		return new;
	end if;	
END;
$$;


ALTER FUNCTION public.fn_verifica_nome() OWNER TO postgres;

--
-- TOC entry 30 (class 1255 OID 18638)
-- Dependencies: 3 407
-- Name: gerasenhausuario(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gerasenhausuario() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	declare
		password varchar(10);
		sequencia char(62);
		letra char;
	begin

		if new.senha = '' then
			password='';
			select into letra substr(sequencia,(round(random()*62))::int,1);
			for i in 1..10 loop
				select INTO letra substr('aAbB0cCdD1eEfF2gGhH3iIjJ4kKlL5mMnN6oOpP7qQrRs8StTu9UwWx0XyYzZ1',(round(random()*62))::int,1);
				password:=password || letra;
			end loop;
			update users set senha=password where id = new.id;
		end if;
		return new;
	end;
$$;


ALTER FUNCTION public.gerasenhausuario() OWNER TO postgres;

--
-- TOC entry 1594 (class 1259 OID 17862)
-- Dependencies: 3 1595
-- Name: area_codarea_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE area_codarea_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.area_codarea_seq OWNER TO postgres;

--
-- TOC entry 1988 (class 0 OID 0)
-- Dependencies: 1594
-- Name: area_codarea_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE area_codarea_seq OWNED BY area.codarea;


--
-- TOC entry 1989 (class 0 OID 0)
-- Dependencies: 1594
-- Name: area_codarea_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('area_codarea_seq', 28, true);


--
-- TOC entry 1602 (class 1259 OID 17904)
-- Dependencies: 1930 1931 1932 339 339 340 340 3 340 339 341 340 337 338 340
-- Name: cliente; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cliente (
    codcliente bigint NOT NULL,
    nomecli dm_nome,
    fantasia dm_nome2,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    fonefixo dm_telefone,
    fonefax dm_telefone,
    fonecelular dm_telefone,
    email dm_local,
    homepage dm_local,
    tipo character(1) NOT NULL,
    datanasctoabertura date,
    cpfcnpj character varying(18),
    rgie character varying(18),
    CONSTRAINT valida_fantasia CHECK (((fantasia)::text <> (nomecli)::text)),
    CONSTRAINT valida_fone CHECK (((fonefixo)::text <> (fonecelular)::text)),
    CONSTRAINT valida_uf CHECK (((length((uf)::bpchar) = 2) AND ((uf)::bpchar <> ' '::bpchar)))
);


ALTER TABLE public.cliente OWNER TO postgres;

--
-- TOC entry 1601 (class 1259 OID 17902)
-- Dependencies: 1602 3
-- Name: cliente_codcliente_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cliente_codcliente_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.cliente_codcliente_seq OWNER TO postgres;

--
-- TOC entry 1990 (class 0 OID 0)
-- Dependencies: 1601
-- Name: cliente_codcliente_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cliente_codcliente_seq OWNED BY cliente.codcliente;


--
-- TOC entry 1991 (class 0 OID 0)
-- Dependencies: 1601
-- Name: cliente_codcliente_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('cliente_codcliente_seq', 16, true);


--
-- TOC entry 1605 (class 1259 OID 17923)
-- Dependencies: 1936 1937 3
-- Name: clientesprofissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE clientesprofissional (
    solicitacaocodsolicitacao bigint NOT NULL,
    profissionalcodprofissional integer DEFAULT 3 NOT NULL,
    aceito boolean,
    nivel smallint NOT NULL,
    CONSTRAINT valida_nivel CHECK (((nivel <= 5) AND (nivel >= 1)))
);


ALTER TABLE public.clientesprofissional OWNER TO postgres;

--
-- TOC entry 1596 (class 1259 OID 17873)
-- Dependencies: 1597 3
-- Name: profissao_codprofissao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissao_codprofissao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.profissao_codprofissao_seq OWNER TO postgres;

--
-- TOC entry 1992 (class 0 OID 0)
-- Dependencies: 1596
-- Name: profissao_codprofissao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissao_codprofissao_seq OWNED BY profissao.codprofissao;


--
-- TOC entry 1993 (class 0 OID 0)
-- Dependencies: 1596
-- Name: profissao_codprofissao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissao_codprofissao_seq', 26, true);


--
-- TOC entry 1598 (class 1259 OID 17884)
-- Dependencies: 1599 3
-- Name: profissional_codprofissional_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissional_codprofissional_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.profissional_codprofissional_seq OWNER TO postgres;

--
-- TOC entry 1994 (class 0 OID 0)
-- Dependencies: 1598
-- Name: profissional_codprofissional_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissional_codprofissional_seq OWNED BY profissional.codprofissional;


--
-- TOC entry 1995 (class 0 OID 0)
-- Dependencies: 1598
-- Name: profissional_codprofissional_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissional_codprofissional_seq', 16, true);


--
-- TOC entry 1604 (class 1259 OID 17917)
-- Dependencies: 1934 1935 3
-- Name: solicitacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE solicitacao (
    codsolicitacao bigint NOT NULL,
    clientecodcliente bigint NOT NULL,
    profissaocodprofissao integer NOT NULL,
    qtde integer,
    datasolicitacao date,
    dataatendimento date,
    CONSTRAINT valida_atendimento CHECK ((dataatendimento > datasolicitacao)),
    CONSTRAINT valida_quantidade CHECK ((qtde > 0))
);


ALTER TABLE public.solicitacao OWNER TO postgres;

--
-- TOC entry 1603 (class 1259 OID 17915)
-- Dependencies: 3 1604
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE solicitacao_codsolicitacao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.solicitacao_codsolicitacao_seq OWNER TO postgres;

--
-- TOC entry 1996 (class 0 OID 0)
-- Dependencies: 1603
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE solicitacao_codsolicitacao_seq OWNED BY solicitacao.codsolicitacao;


--
-- TOC entry 1997 (class 0 OID 0)
-- Dependencies: 1603
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('solicitacao_codsolicitacao_seq', 8, true);


--
-- TOC entry 1624 (class 1259 OID 18700)
-- Dependencies: 1939 1940 1941 1942 1943 340 337 3
-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE users (
    id integer NOT NULL,
    nome dm_nome,
    tipo character(1),
    codigo integer,
    email dm_local,
    ativo boolean DEFAULT true,
    login character varying(10),
    senha character varying(15) DEFAULT ''::character varying,
    CONSTRAINT users_tipo_check CHECK ((((tipo = 'C'::bpchar) OR (tipo = 'P'::bpchar)) OR (tipo = 'O'::bpchar))),
    CONSTRAINT valida_login CHECK ((length((login)::text) > 0)),
    CONSTRAINT valida_senha CHECK ((length((senha)::text) >= 3))
);


ALTER TABLE public.users OWNER TO postgres;

--
-- TOC entry 1623 (class 1259 OID 18698)
-- Dependencies: 1624 3
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    MINVALUE 0
    CACHE 1;


ALTER TABLE public.users_id_seq OWNER TO postgres;

--
-- TOC entry 1998 (class 0 OID 0)
-- Dependencies: 1623
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE users_id_seq OWNED BY users.id;


--
-- TOC entry 1999 (class 0 OID 0)
-- Dependencies: 1623
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('users_id_seq', 13, true);


--
-- TOC entry 1608 (class 1259 OID 17977)
-- Dependencies: 1714 337 3 337
-- Name: vw_cliente_profissional; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, cp.nivel, cp.aceito FROM (((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente)));


ALTER TABLE public.vw_cliente_profissional OWNER TO postgres;

--
-- TOC entry 1609 (class 1259 OID 17982)
-- Dependencies: 1715 337 337 337 3 337
-- Name: vw_cliente_profissional_geral; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional_geral AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, prof.codprofissao, prof.descricao AS profissao, a.codarea, a.descricao AS area, cp.nivel, cp.aceito FROM (((((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente))) JOIN profissao prof ON ((prof.codprofissao = s.profissaocodprofissao))) JOIN area a ON ((a.codarea = prof.areacodarea)));


ALTER TABLE public.vw_cliente_profissional_geral OWNER TO postgres;

--
-- TOC entry 1616 (class 1259 OID 18643)
-- Dependencies: 1721 342 3 343 341 340 340 340 339 339 338 337
-- Name: vw_profissionais_disponiveis; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_profissionais_disponiveis AS
    SELECT profissional.codprofissional, profissional.cpf, profissional.nome, profissional.fantasia, profissional.sexo, profissional.fonefixo, profissional.fonecel, profissional.endereco, profissional.bairro, profissional.cidade, profissional.uf, profissional.rg_num, profissional.rg_org_exp, profissional.rg_data_exp, profissional.carteiraprofissional, profissional.cnh_registro, profissional.cnh_cat, profissional.cnh_vencto, profissional.datanascimento, profissional.alocado FROM profissional WHERE (profissional.alocado = false);


ALTER TABLE public.vw_profissionais_disponiveis OWNER TO postgres;

--
-- TOC entry 1617 (class 1259 OID 18652)
-- Dependencies: 1722 337 3 337
-- Name: vw_solicitacoes; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_solicitacoes AS
    SELECT solicitacao.codsolicitacao, cliente.codcliente, cliente.nomecli AS cliente, solicitacao.qtde AS quantidade, profissao.codprofissao, profissao.descricao AS profissao, solicitacao.datasolicitacao, solicitacao.dataatendimento FROM ((solicitacao JOIN profissao ON ((profissao.codprofissao = solicitacao.profissaocodprofissao))) JOIN cliente ON ((cliente.codcliente = solicitacao.clientecodcliente)));


ALTER TABLE public.vw_solicitacoes OWNER TO postgres;

--
-- TOC entry 1619 (class 1259 OID 18664)
-- Dependencies: 1723 3 337
-- Name: vw_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, count(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1610 (class 1259 OID 17995)
-- Dependencies: 1716 3 337
-- Name: vw_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissional_solicitacao AS
    SELECT vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional, count(vw_cliente_profissional.codprofissional) AS quantia FROM vw_cliente_profissional GROUP BY vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional;


ALTER TABLE public.vw_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1620 (class 1259 OID 18668)
-- Dependencies: 1724 337 3
-- Name: vw_soma_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_soma_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, sum(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_soma_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1611 (class 1259 OID 17999)
-- Dependencies: 1717 337 3
-- Name: vwr_area_quantidade; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_area_quantidade AS
    SELECT vw_area_profissao.area, count(vw_area_profissao.profissao) AS quantidade FROM vw_area_profissao GROUP BY vw_area_profissao.area ORDER BY count(vw_area_profissao.profissao) DESC, vw_area_profissao.area;


ALTER TABLE public.vwr_area_quantidade OWNER TO postgres;

--
-- TOC entry 1614 (class 1259 OID 18015)
-- Dependencies: 1720 337 3
-- Name: vwr_qtd_area_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_area_solicitacao AS
    SELECT vw_cliente_profissional_geral.area, count(vw_cliente_profissional_geral.codarea) AS quantia FROM vw_cliente_profissional_geral GROUP BY vw_cliente_profissional_geral.area ORDER BY count(vw_cliente_profissional_geral.codarea) DESC, vw_cliente_profissional_geral.area;


ALTER TABLE public.vwr_qtd_area_solicitacao OWNER TO postgres;

--
-- TOC entry 1621 (class 1259 OID 18672)
-- Dependencies: 1725 3 337
-- Name: vwr_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissao_solicitacao AS
    SELECT vw_qtd_profissao_solicitacao.profissao, vw_qtd_profissao_solicitacao.quantia FROM vw_qtd_profissao_solicitacao ORDER BY vw_qtd_profissao_solicitacao.quantia DESC, vw_qtd_profissao_solicitacao.profissao;


ALTER TABLE public.vwr_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1613 (class 1259 OID 18011)
-- Dependencies: 1719 337 3
-- Name: vwr_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissional_solicitacao AS
    SELECT vw_qtd_profissional_solicitacao.nomeprofissional, vw_qtd_profissional_solicitacao.quantia FROM vw_qtd_profissional_solicitacao ORDER BY vw_qtd_profissional_solicitacao.quantia DESC, vw_qtd_profissional_solicitacao.nomeprofissional;


ALTER TABLE public.vwr_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1622 (class 1259 OID 18676)
-- Dependencies: 1726 3 337
-- Name: vwr_soma_cliente_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_soma_cliente_solicitacao AS
    SELECT vw_solicitacoes.cliente, sum(vw_solicitacoes.quantidade) AS quantidade FROM vw_solicitacoes GROUP BY vw_solicitacoes.cliente ORDER BY sum(vw_solicitacoes.quantidade) DESC, vw_solicitacoes.cliente;


ALTER TABLE public.vwr_soma_cliente_solicitacao OWNER TO postgres;

--
-- TOC entry 1918 (class 2604 OID 17867)
-- Dependencies: 1595 1594 1595
-- Name: codarea; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE area ALTER COLUMN codarea SET DEFAULT nextval('area_codarea_seq'::regclass);


--
-- TOC entry 1929 (class 2604 OID 17907)
-- Dependencies: 1601 1602 1602
-- Name: codcliente; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE cliente ALTER COLUMN codcliente SET DEFAULT nextval('cliente_codcliente_seq'::regclass);


--
-- TOC entry 1919 (class 2604 OID 17878)
-- Dependencies: 1596 1597 1597
-- Name: codprofissao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissao ALTER COLUMN codprofissao SET DEFAULT nextval('profissao_codprofissao_seq'::regclass);


--
-- TOC entry 1920 (class 2604 OID 17889)
-- Dependencies: 1599 1598 1599
-- Name: codprofissional; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissional ALTER COLUMN codprofissional SET DEFAULT nextval('profissional_codprofissional_seq'::regclass);


--
-- TOC entry 1933 (class 2604 OID 17920)
-- Dependencies: 1604 1603 1604
-- Name: codsolicitacao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE solicitacao ALTER COLUMN codsolicitacao SET DEFAULT nextval('solicitacao_codsolicitacao_seq'::regclass);


--
-- TOC entry 1938 (class 2604 OID 18703)
-- Dependencies: 1624 1623 1624
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);


--
-- TOC entry 1976 (class 0 OID 17864)
-- Dependencies: 1595
-- Data for Name: area; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO area (codarea, descricao) VALUES (1, 'Informatica');
INSERT INTO area (codarea, descricao) VALUES (2, 'Biológica');
INSERT INTO area (codarea, descricao) VALUES (3, 'Pornográfica');


--
-- TOC entry 1980 (class 0 OID 17904)
-- Dependencies: 1602
-- Data for Name: cliente; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (1, 'Eidy', 'nenhuma', 'Endereco?', 'Bairro?', 'Londrina', 'PR', '(99) 9999-9999', '(88) 8888-8888', '(77) 7777-7777', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-06-23', '012345678901234567', '012345678901234567');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (2, 'Shishido', 'nenhuma', 'desconhecido', 'nao se sabe', 'nao se faz ideia', 'PR', '(22) 2222-2222', '(11) 1111-1111', '(00) 0000-0000', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-06-23', '222222222222222222', '222222222222222222');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (7, 'Teste Cliente', 'client', 'a', 'b', 'c', 'PR', '(22) 2222-2222', '(33) 3333-3333', NULL, 'sa', 'ee', 'g', '2011-06-01', '333333333333333333', '333333333333333333');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (9, 'Teste Cliente2', 'client2', 'a', 'b', 'c', 'PR', '(22) 2222-2222', '(33) 3333-3333', NULL, 'sa', 'ee', 'g', '2011-06-01', '444444444444444444', '333333333333333333');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (16, 'Teste Cliente', 'Cliente', 'a', 'b', 'c', 'PR', '(22) 2222-2222', '(33) 3333-3333', NULL, 'ddd', '222', '1', '2011-06-23', '222222222', '22');


--
-- TOC entry 1982 (class 0 OID 17923)
-- Dependencies: 1605
-- Data for Name: clientesprofissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (5, 3, true, 3);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 1, true, 2);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 4, true, 1);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (4, 2, true, 5);


--
-- TOC entry 1979 (class 0 OID 17897)
-- Dependencies: 1600
-- Data for Name: especializacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 1, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 2, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 6, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 1, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 3, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 7, '2009-01-01', 4);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 4, '2009-01-01', 1);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 4, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 5, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 8, '2009-01-01', 2);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 8, '2009-01-01', 1);


--
-- TOC entry 1977 (class 0 OID 17875)
-- Dependencies: 1597
-- Data for Name: profissao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (1, 'Analista De Sistemas', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (2, 'Programador', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (3, 'Cara Das Rede', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (4, 'Médico', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (5, 'Dentista', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (6, 'Cirurgião', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (7, 'Cafetão', 3);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (8, 'Ator', 3);


--
-- TOC entry 1978 (class 0 OID 17886)
-- Dependencies: 1599
-- Data for Name: profissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (3, '333.333.333-33', 'Marcio Edsao', 'nenhuma', 'm', '(33) 3333-3333', '(33) 3456-3456', 'Rua Qualquer', 'Centro', 'Maracai', 'PR', '3.333.333-33', 'naosei', '1957-06-24', '33333333333333', '333333333333', '3333333333', '1959-12-11', '1955-01-01', true);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (1, '111.111.111-11', 'Diogo Hideki Matsumoto', 'nenhuma', 'm', '(99) 9999-9999', '(11) 1234-1234', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '1995-09-09', '01234567890123', '012345678901', '0123456789', '1998-02-25', '1991-07-26', true);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (4, '444.444.444-44', 'Pablo Paulo da Silva', 'nenhuma', 'm', '(44) 4444-4444', '(44) 4567-4567', 'Rua Qualquer', 'Centro', 'Cornélio', 'PR', '4.444.444-44', 'naosei', '1992-08-08', '44444444444444', '444444444444', '4444444444', '1995-01-25', '1989-03-09', true);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (2, '222.222.222-22', 'Aline Yukita', 'nenhuma', 'f', '(22) 2222-2222', '(22) 2345-2345', 'Rua Qualquer', 'Centro', 'Assaí', 'PR', '2.222.222-22', 'naosei', '1997-05-05', '22222222222222', '222222222222', '2222222222', '1999-10-22', '1991-02-06', true);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (6, '123.124.124-11', 'Testador', 'f', 'm', '(11) 1111-1111', '(22) 2222-2222', 'q', 'e', 'd', 'PR', '222121111111', 'w     ', '2011-06-09', 'e', 'q', 'f', '2011-06-03', '2011-06-03', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (9, '655.656.565-65', 'Testador', 'e', 'm', '(22) 2222-2222', '(33) 3333-3333', 'a', 'q', 'f', 'PR', '231231312312', 'e     ', '2011-06-09', 'e', 'f', 'q', '1993-12-03', '1993-12-03', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (11, '333.333.333-44', 'Testador', 'e', 'm', '(22) 2222-2222', '(33) 3333-3333', 'a', 'q', 'f', 'PR', '231231312312', 'e     ', '2011-06-09', 'e', 'f', 'q', '1993-12-03', '1993-12-03', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (14, '131.313.213-21', 'Testador2', 'e', 'm', '(22) 2222-2222', '(33) 3333-3333', 'a', 'q', 'f', 'PR', '231231312312', 'e     ', '2011-06-09', 'e', 'f', 'q', '1993-12-03', '1993-12-03', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (16, '123.123.113-12', 'Testador3', 'e', 'm', '(22) 2222-2222', '(33) 3333-3333', 'a', 'q', 'f', 'PR', '231231312312', 'e     ', '2011-06-09', 'e', 'f', 'q', '1993-12-03', '1993-12-03', false);


--
-- TOC entry 1981 (class 0 OID 17917)
-- Dependencies: 1604
-- Data for Name: solicitacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (1, 1, 1, 2, '2011-06-08', '2011-06-11');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (2, 1, 2, 4, '2011-06-18', '2011-06-19');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (3, 1, 3, 2, '2011-06-18', '2011-06-19');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (7, 1, 2, 1, '2011-06-26', NULL);
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (5, 2, 5, 1, '2011-06-15', '2011-06-28');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (6, 2, 8, 2, '2011-06-21', '2011-06-28');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (4, 2, 4, 1, '2011-06-15', '2011-06-28');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (8, 2, 1, 3, '2011-06-28', NULL);


--
-- TOC entry 1983 (class 0 OID 18700)
-- Dependencies: 1624
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (1, 'Diogo', 'P', 1, 'aaa', true, 'diogo', 'senha');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (2, 'Eidy', 'C', 1, 'eid@cliente', true, 'eid', 'eid');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (3, 'Aidimim', 'O', 0, NULL, true, 'admin', 'admin');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (5, 'Testador', 'P', 9, NULL, true, 'teste', 'teste');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (6, 'Testador', 'P', 11, NULL, true, 'tester', 'teste');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (8, 'Testador2', 'P', 14, NULL, true, 'tester2', 'teste');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (9, 'Testador3', 'P', 16, NULL, true, 'tester3', 'teste');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (12, 'Teste Cliente', 'C', 16, NULL, true, 'cliente', 'cliente');
INSERT INTO users (id, nome, tipo, codigo, email, ativo, login, senha) VALUES (13, 'Henrique', 'C', 2, 'Shishidao@utfpr.com.br', true, 'shi', 'shi');


--
-- TOC entry 1945 (class 2606 OID 17872)
-- Dependencies: 1595 1595
-- Name: area_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_pkey PRIMARY KEY (codarea);


--
-- TOC entry 1957 (class 2606 OID 17914)
-- Dependencies: 1602 1602
-- Name: cliente_cpfcnpj_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_cpfcnpj_key UNIQUE (cpfcnpj);


--
-- TOC entry 1959 (class 2606 OID 17912)
-- Dependencies: 1602 1602
-- Name: cliente_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_pkey PRIMARY KEY (codcliente);


--
-- TOC entry 1963 (class 2606 OID 17928)
-- Dependencies: 1605 1605 1605
-- Name: clientesprofissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT clientesprofissional_pkey PRIMARY KEY (solicitacaocodsolicitacao, profissionalcodprofissional);


--
-- TOC entry 1955 (class 2606 OID 17901)
-- Dependencies: 1600 1600 1600
-- Name: especializacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT especializacao_pkey PRIMARY KEY (profissionalcodprofissional, profissaocodprofissao);


--
-- TOC entry 1947 (class 2606 OID 17883)
-- Dependencies: 1597 1597
-- Name: profissao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT profissao_pkey PRIMARY KEY (codprofissao);


--
-- TOC entry 1951 (class 2606 OID 17896)
-- Dependencies: 1599 1599
-- Name: profissional_cpf_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_cpf_key UNIQUE (cpf);


--
-- TOC entry 1953 (class 2606 OID 17894)
-- Dependencies: 1599 1599
-- Name: profissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_pkey PRIMARY KEY (codprofissional);


--
-- TOC entry 1961 (class 2606 OID 17922)
-- Dependencies: 1604 1604
-- Name: solicitacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT solicitacao_pkey PRIMARY KEY (codsolicitacao);


--
-- TOC entry 1949 (class 2606 OID 19230)
-- Dependencies: 1597 1597
-- Name: unq_profissao; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT unq_profissao UNIQUE (descricao);


--
-- TOC entry 1965 (class 2606 OID 18711)
-- Dependencies: 1624 1624
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- TOC entry 1974 (class 2620 OID 18607)
-- Dependencies: 21 1605
-- Name: geraatualizacaonivelprofissional; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER geraatualizacaonivelprofissional
    BEFORE UPDATE ON clientesprofissional
    FOR EACH ROW
    EXECUTE PROCEDURE atualizanivelprofissional();


--
-- TOC entry 1975 (class 2620 OID 19247)
-- Dependencies: 52 1605
-- Name: tg_atualizaalocacaoprofissional; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tg_atualizaalocacaoprofissional
    AFTER INSERT OR UPDATE ON clientesprofissional
    FOR EACH ROW
    EXECUTE PROCEDURE atualizaalocacaoprofissional();


--
-- TOC entry 1973 (class 2620 OID 19232)
-- Dependencies: 1597 48
-- Name: tg_verifica_nome; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tg_verifica_nome
    BEFORE INSERT OR UPDATE ON profissao
    FOR EACH ROW
    EXECUTE PROCEDURE fn_verifica_nome();


--
-- TOC entry 1971 (class 2606 OID 17954)
-- Dependencies: 1604 1960 1605
-- Name: fkclientespr183846; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr183846 FOREIGN KEY (solicitacaocodsolicitacao) REFERENCES solicitacao(codsolicitacao);


--
-- TOC entry 1972 (class 2606 OID 17959)
-- Dependencies: 1599 1605 1952
-- Name: fkclientespr72559; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr72559 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1967 (class 2606 OID 17934)
-- Dependencies: 1600 1599 1952
-- Name: fkespecializ744318; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ744318 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1968 (class 2606 OID 17939)
-- Dependencies: 1600 1946 1597
-- Name: fkespecializ926790; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ926790 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1966 (class 2606 OID 17929)
-- Dependencies: 1595 1597 1944
-- Name: fkprofissao66504; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT fkprofissao66504 FOREIGN KEY (areacodarea) REFERENCES area(codarea);


--
-- TOC entry 1970 (class 2606 OID 17949)
-- Dependencies: 1946 1597 1604
-- Name: fksolicitaca640088; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca640088 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1969 (class 2606 OID 17944)
-- Dependencies: 1958 1602 1604
-- Name: fksolicitaca741811; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca741811 FOREIGN KEY (clientecodcliente) REFERENCES cliente(codcliente);


--
-- TOC entry 1987 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-06-28 23:39:43 BRT

--
-- PostgreSQL database dump complete
--

